//: -L+sqlite3d  -version=PRINT_MIXIN 
import 
	tango.time.Clock,
	tango.io.Stdout,
	sqlite3d.all;

struct Seeds{
	static const TableDef def	=
		{
			"seeds1", 
			[
				{DataType.UINT,		"uid",			ColType.ROWID },
				{DataType.UINT,		"aid",			ColType.NOT_NULL },
				{DataType.UINT,		"cid",			ColType.NOT_NULL },
				{DataType.UINT,		"size",		ColType.NULL },
				{DataType.UINT,		"red",			ColType.NULL },
				
				{DataType.TEXT16,	"title",		ColType.NULL },
				{DataType.TEXT,	"title2",		ColType.NULL },
				{DataType.BLOB,		"intro",		ColType.NULL },
				
				{DataType.UINT,		"save_time",	ColType.NULL },
				{DataType.UINT,		"fetch_time",	ColType.NULL },
			]
		};
	
	mixin DefTable!(Seeds);

	mixin DefMask!(Seeds,  
			"all", 	 0xfff 
			, "t1",   _aid |  _title2 | _title | _uid
		);

}


static const int times = 1_001;

void  main(){
	te3;
	scope con		= Connection.open(":memory:");
	//scope con		= Connection.open("x.db3");
	scope(exit) delete con;
	con.exec("PRAGMA journal_mode = MEMORY");
	con.exec(Seeds.sql_create);
	scope stmt	= Statement.Create(con);
	scope(exit) delete stmt;
		
	alias Seeds.Row_all Row1;
	scope p	= new Row1;
	p.title	= "中华人民共和国"w ;
	p.title2	= "美丽坚和众国"c ;
		
	auto now	= Clock.now;
	con.begin();
	for(int _i =0; _i < times; _i++){
		p.uid		= _i + 100;
		p.aid		= _i + 1;
		p.cid		= _i* 11;
		p.size	= uint.max - _i;
		Row1.Insert(stmt, p);
	}
	con.commit();
	Stdout.formatln("用时{}ms, {}条", (Clock.now-now).millis, times).flush;
	
	if( Row1.One(stmt, p, 104) ){
		dump(p);
		p.aid	++;
		Row1.Update(stmt, p);
	}
	test1();
	test();
}
void  test1(){
	scope con		= Connection.open(":memory:");
	//scope con		= Connection.open("x.db3");
	scope(exit) delete con;
	con.exec("PRAGMA journal_mode = MEMORY");
	con.exec(Seeds.sql_create);
	scope stmt	= Statement.Create(con);
	scope(exit) delete stmt;
		
	alias Seeds.Row_all Row1;
	scope p	= new Row1;
	p.title	= "中华人民共和国"w ;
	p.title2	= "美丽坚和众国"c ;
	
	Row1.pre_insert(stmt);
	auto now	= Clock.now;
	con.begin();
	for(int _i =0; _i < times; _i++){
		p.uid		= _i + 100;
		p.aid		= _i + 1;
		p.cid		= _i* 11;
		p.size	= uint.max - _i;
		Row1.step_insert(stmt, p);
	}
	con.commit();
	Stdout.formatln("用时{}ms, {}条", (Clock.now-now).millis, times).flush;
	
	scope stmt2	= Statement.Create(con);
	scope(exit) delete stmt2;
	Row1.pre_update(stmt2);
	if( Row1.One(stmt, p, 104) ){
		dump(p);
		p.aid	++;
		Row1.step_update(stmt2, p);
	}
}

void dump(T)(T* p){
	assert(p !is null);
	foreach(int i, c; p.tupleof){
		Stdout.formatln("{}, {}", typeof(*p).tupleof[i].stringof , c);
	}
	Stdout.flush;
}


void test(){
	scope con		= Connection.open(":memory:");
	//scope con		= Connection.open("x.db3");
	scope(exit) delete con;
	con.exec("PRAGMA journal_mode = MEMORY");
	con.exec(Seeds.sql_create);
	scope stmt	= Statement.Create(con);
	scope(exit) delete stmt;
	scope stmt2	= Statement.Create(con);
	scope(exit) delete stmt2;

	scope row	=  new Seeds.Row_all;
	alias RowInsert!(Seeds.Row_all) all_insert;
	all_insert.compile(stmt);
	auto now	= Clock.now;
	con.begin();
	for(int _i =0; _i < times; _i++){
		row.title	= "中华人民共和国"w ;
		row.title2	= "美丽坚和众国"c ;
		row.uid		= _i + 100;
		row.aid		= _i + 1;
		row.cid		= _i* 11;
		row.size	= uint.max - _i;
		all_insert.step(stmt, row);
	}
	con.commit();
	Stdout.formatln("用时{}ms, {}条", (Clock.now-now).millis, times).flush;
	
	alias Seeds.Row_t1 Row1;
	alias RowUpdate!(Row1, "uid=?", uint) t2_update;
	t2_update.compile(stmt2);
	
	alias RowSelect!(Row1, "uid>? ORDER BY uid ASC LIMIT ?, ?", uint,  int, int) t2_select;
	Stdout.formatln("统计{}条",  t2_select.count(stmt, 105) ).flush;
	t2_select.select(stmt, 105, 0, 3);
	scope p	=  new Row1;
	int i ;
	while(t2_select.fetchOne(stmt, p) ){
		p.aid++;
		t2_update.step(stmt2, p, p.uid );
		Stdout.formatln("{}", i++);
		dump(p);
	}
}


static const te31	= `PRAGMA journal_mode = MEMORY;
CREATE table t1(
	a
);

INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (1);

INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (2);

INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (3);

INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (3);`c;

void te3(){
	scope con		= Connection.open(":memory:");
	con.exec(te31);
	auto s1	= new TSelect!("select a,count(a) as b from t1 where ROWID > ? group by a order by b desc limit ?, ?;"
					, int,int, int, int,int)(con);
	Stdout.formatln( "count:{}",  s1.count(0) );
	s1.select(0, 0, 10);
	typeof(s1).Tr tr;
	while( s1.fetchOne(tr) ){
		Stdout.formatln( "{}, {}",  tr[0] , tr[1] );
	}
}
